FGS.database = {};
FGS.database.db = null;

FGS.database.open = function(userID)
{
	var prefs = Components.classes["@mozilla.org/preferences-service;1"]
						.getService(Components.interfaces.nsIPrefService);
	prefs = prefs.getBranch("extensions.FGS.");
	
	var a = false;
	
	try
	{
		a = prefs.getBoolPref("uninstall");
	}
	catch(e)
	{
		a = false;
	}
	
	if(a)
	{
		var dir_old = Components.classes["@mozilla.org/file/directory_service;1"]
							 .getService(Components.interfaces.nsIProperties)
							 .get("ProfD", Components.interfaces.nsIFile);
		
		dir_old.append("FGS");	
		
		
		var dir_root = Components.classes["@mozilla.org/file/directory_service;1"]
							 .getService(Components.interfaces.nsIProperties)
							 .get("ProfD", Components.interfaces.nsIFile);
		
		if( dir_old.exists() && dir_old.isDirectory() )
		{
			dir_old.moveTo(dir_root, "FGS-old"+Math.random());
			//dir.remove(true);
		}
		
		prefs.setBoolPref("uninstall", false);
	}
	
	
	var dir = Components.classes["@mozilla.org/file/directory_service;1"]
						 .getService(Components.interfaces.nsIProperties)
						 .get("ProfD", Components.interfaces.nsIFile);
	dir.append("FGS");
	
	var file = Components.classes["@mozilla.org/file/directory_service;1"]
						 .getService(Components.interfaces.nsIProperties)
						 .get("ProfD", Components.interfaces.nsIFile);
						 
	if( !dir.exists() || !dir.isDirectory() )
	{
		dir.create(Components.interfaces.nsIFile.DIRECTORY_TYPE, 0777);
	}
	file.append(userID+".sqlite");
	
	if(file.exists())
	{
		file.moveTo(dir,'');
	}
	else
	{
		var file = Components.classes["@mozilla.org/file/directory_service;1"]
							 .getService(Components.interfaces.nsIProperties)
							 .get("ProfD", Components.interfaces.nsIFile);
		file.append("FGS");
		file.append(userID+".sqlite");
	}
	
	var storageService = Components.classes["@mozilla.org/storage/service;1"]
							.getService(Components.interfaces.mozIStorageService);
	
	FGS.database.db = storageService.openDatabase(file); // Will also create the file if it does not exist
}

FGS.database.createTable = function()
{
	FGS.database.db.executeSimpleSQL('CREATE TABLE IF NOT EXISTS options (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, option LONGTEXT)');
	
	FGS.database.db.executeSimpleSQL('CREATE TABLE IF NOT EXISTS ' + 
			'bonuses (id TEXT PRIMARY KEY ASC, gameID INTEGER, status INTEGER, error TEXT, title TEXT, text TEXT, image TEXT, url TEXT, time INTEGER, feedback TEXT, link_data TEXT, like_bonus INTEGER, comment_bonus INTEGER, resend_gift TEXT, error_text TEXT)');
	
	FGS.database.db.executeSimpleSQL('CREATE TABLE IF NOT EXISTS ' + 
                  'neighborStats (userID INTEGER, gameID INTEGER, lastBonus INTEGER,  lastGift INTEGER, totalBonuses INTEGER, totalGifts INTEGER, lastGiftSent INTEGER, totalGiftsSent INTEGER, PRIMARY KEY(userID, gameID))');
	
	FGS.database.db.executeSimpleSQL('CREATE TABLE IF NOT EXISTS ' + 
			'requests(id TEXT PRIMARY KEY ASC, gameID INTEGER, status INTEGER, error TEXT, title TEXT, text TEXT, image TEXT, post TEXT, time INTEGER, resend_gift TEXT, error_text TEXT)');

	FGS.database.db.executeSimpleSQL('CREATE TABLE IF NOT EXISTS ' + 
			'freegifts(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, gameID INTEGER, friend TEXT, gift TEXT, time INTEGER, is_thank_you INTEGER)');
	
	
	try {	FGS.database.db.executeSimpleSQL("INSERT OR IGNORE INTO options VALUES(1,'{}')"); } catch(e) { }

	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE neighborStats ADD COLUMN lastGiftSent INTEGER');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE neighborStats ADD COLUMN totalGiftsSent INTEGER');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE bonuses ADD COLUMN comment_bonus INTEGER');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE bonuses ADD COLUMN resend_gift TEXT');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE bonuses ADD COLUMN error_text TEXT');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE requests ADD COLUMN resend_gift TEXT');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE requests ADD COLUMN error_text TEXT');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL('ALTER TABLE freegifts ADD COLUMN is_thank_you INTEGER');	} catch(e) {}
	try	{	FGS.database.db.executeSimpleSQL("UPDATE neighborStats SET lastGiftSent = 0 where lastGiftSent IS NULL");	} catch(e) {}	
	try	{	FGS.database.db.executeSimpleSQL("UPDATE neighborStats SET totalGiftsSent = 0 where totalGiftsSent IS NULL");	} catch(e) {}
	
	FGS.databaseAlreadyOpen = true;
	
	if(FGS.optionsLoaded == false)
	{
		FGS.loadOptions(FGS.userID);
	}
}

FGS.database.updateStats = function(type, data2)
{
	for(var ids in data2)
	{
		FGS.database.addStats(type, ids, data2[ids]);
	}
}

FGS.database.addStats = function(type, ids, data)
{
	var x = ids.split('_');
	var userID = parseInt(x[0]);
	var gameID = parseInt(x[1]);
	
	var time = data.time;
	var count = parseInt(data.count);
	
	var stmt = FGS.database.db.createStatement("INSERT INTO neighborStats (userID, gameID, lastBonus, lastGift, totalBonuses, totalGifts, lastGiftSent, totalGiftsSent) VALUES(:userID, :gameID, :lastBonus, :lastGift, :totalBonuses, :totalGifts, :lastGiftSent, :totalGiftsSent)");
	
	stmt.params.gameID = gameID;
	stmt.params.userID = userID;
	
	if(type == 'bonus')
	{
		stmt.params.lastBonus = time;
		stmt.params.lastGift  = 0;
		stmt.params.totalBonuses = count;
		stmt.params.totalGifts = 0;
		stmt.params.lastGiftSent = 0;
		stmt.params.totalGiftsSent = 0;
	}
	else if(type == 'giftSent')
	{
		stmt.params.lastBonus = 0;
		stmt.params.lastGift  = 0;
		stmt.params.totalBonuses = 0;
		stmt.params.totalGifts = 0;
		stmt.params.lastGiftSent = time;
		stmt.params.totalGiftsSent = count;
	}
	else
	{
		stmt.params.lastBonus = 0;
		stmt.params.lastGift  = time;
		stmt.params.totalBonuses = 0;
		stmt.params.totalGifts = count;
		stmt.params.lastGiftSent = 0;
		stmt.params.totalGiftsSent = 0;
	}
	
	stmt.executeAsync({
		handleResult: function(aReason) 
		{
		},
		handleError: function(aError) 
		{
			if(type == 'bonus')
				var stmt2 = FGS.database.db.createStatement("UPDATE neighborStats SET totalBonuses = (totalBonuses + :count), lastBonus = :lastBonus where gameID = :gameID AND userID = :userID");
			else if(type == 'giftSent')
				var stmt2 = FGS.database.db.createStatement("UPDATE neighborStats SET totalGiftsSent = (totalGiftsSent + :count), lastGiftSent = :lastGiftSent where gameID = :gameID AND userID = :userID");
			else
				var stmt2 = FGS.database.db.createStatement("UPDATE neighborStats SET totalGifts = (totalGifts + :count), lastGift = :lastGift where gameID = :gameID AND userID = :userID");
			
			stmt2.params.userID = userID;
			stmt2.params.gameID = gameID;
			stmt2.params.count = count;
			if(type == 'bonus')
				stmt2.params.lastBonus = time;
			else if(type == 'giftSent')
				stmt2.params.lastGiftSent = time;
			else
				stmt2.params.lastGift = time;
			
			stmt2.executeAsync();
		},

		handleCompletion: function(aReason) 
		{
		}
	});
};

FGS.database.addBonus = function(data2)
{
	var checkArr = [];
	
	FGS.jQuery(data2).each(function(k, data)
	{
		checkArr.push(data[0]);
	});
	
	var stmtBigArr = [];
	
	
	while(checkArr.length > 0)
	{
		var tmpArr = checkArr.slice(0,100);
		var where = ' where id = "'+tmpArr.join('" or id = "')+'" ';
		checkArr = checkArr.slice(100);
		var stmt = FGS.database.db.createStatement("SELECT id FROM bonuses"+where);
		
		stmtBigArr.push(stmt);
	}
	var i =0;
	
	FGS.database.db.executeAsync(stmtBigArr, stmtBigArr.length, 
	{
		results: [],
		handleResult: function(aResultSet) 
		{
			for (var row = aResultSet.getNextRow(); row; row = aResultSet.getNextRow())
			{
				this.results.push(row.getResultByIndex(0));
			}
		},

		handleError: function(aError) 
		{
			//dump("Error: " + aError.message);
		},

		handleCompletion: function(aReason) 
		{
			var results = this.results;
			
			var stmtArr = [];
			
			var outArr = [];
			var updStatObj = {};
			var newElements = 0;
			
			FGS.jQuery(data2).each(function(k, data)
			{
				if(FGS.jQuery.inArray(data[0], results) > -1)
					return;
				
				try
				{
					var tmpObj = JSON.parse(data[7]);
					
					var userID =  tmpObj.actor || tmpObj.actrs;
					var gameID = tmpObj.source_app_id || tmpObj.app_id;					
					var time = tmpObj.pub_time|| tmpObj.content_timestamp;
					
					if(typeof(updStatObj[userID+'_'+gameID]) == 'undefined')
					{
						updStatObj[userID+'_'+gameID] = {count: 1, time: time};
					}
					else
					{
						updStatObj[userID+'_'+gameID].count++;
						if(time > updStatObj[userID+'_'+gameID].time)
							updStatObj[userID+'_'+gameID].time = time;
					}
				}
				catch(e)
				{}
				
				outArr.push(data);
				
				if(FGS.giftlistFocus == false)
					newElements++;
				
				try
				{
					var stmt = FGS.database.db.createStatement("INSERT OR IGNORE INTO bonuses VALUES (:d1,:d2,:e0,:e1,:d3,:d4,:d5,:d6,:d7,:d8,:d9,:e2,:e3,:e4,:e5)");
					
					stmt.params.e0 = stmt.params.e2 = stmt.params.e3 = 0;		
					stmt.params.e1 = stmt.params.e4 = stmt.params.e5 = '';
					
					for(var i in data)
					{
						var j = parseInt(i)+1;
						
						if(typeof(data[i]) == 'undefined')
							data[i] = '';
						
						stmt.params['d'+j.toString()] = data[i];
					}
					stmtArr.push(stmt);
				}
				catch(a)
				{
				}
			});
			
			FGS.database.db.executeAsync(stmtArr, stmtArr.length, 
			{
				handleCompletion: function(aReason) 
				{
					if(outArr.length > 0)
					{
						FGS.sendView('addNewBonus', '', '', outArr);
						FGS.newElements += newElements;
						FGS.database.updateStats('bonus', updStatObj);
					}
					FGS.updateIcon();
				}
			});
		}
	});
}

FGS.database.addRequest = function(data2)
{
	var checkArr = [];
	
	FGS.jQuery(data2).each(function(k, data)
	{
		checkArr.push(data[0]);
	});
	
	var stmtBigArr = [];
	
	
	while(checkArr.length > 0)
	{
		var tmpArr = checkArr.slice(0,100);
		var where = ' where id = "'+tmpArr.join('" or id = "')+'" ';
		checkArr = checkArr.slice(100);
		
		var stmt = FGS.database.db.createStatement("SELECT id FROM requests"+where);
		
		stmtBigArr.push(stmt);
	}
	var i =0;
	
	FGS.database.db.executeAsync(stmtBigArr, stmtBigArr.length, 
	{
		results: [],
		handleResult: function(aResultSet) 
		{
			for (var row = aResultSet.getNextRow(); row; row = aResultSet.getNextRow())
			{
				this.results.push(row.getResultByIndex(0));
			}
		},

		handleError: function(aError) 
		{
			//dump("Error: " + aError.message);
		},

		handleCompletion: function(aReason) 
		{
			var results = this.results;
			
			var stmtArr = [];
			
			var outArr = [];
			var updStatObj = {};
			
			var newElements = 0;
		
			FGS.jQuery(data2).each(function(k, data)
			{
				if(FGS.jQuery.inArray(data[0], results) > -1)
					return;
				
				var newItem = data[data.length-1];
				var data = data.slice(0,data.length-1);
				
				try
				{
					if(newItem.length > 0)
					{
						var userID = newItem[0];
						var gameID = newItem[1];						
						var time   = newItem[2];
						
						if(typeof(updStatObj[userID+'_'+gameID]) == 'undefined')
						{
							updStatObj[userID+'_'+gameID] = {count: 1, time: time};
						}
						else
						{
							updStatObj[userID+'_'+gameID].count++;
							if(time > updStatObj[userID+'_'+gameID].time)
								updStatObj[userID+'_'+gameID].time = time;
						}
					}
				}
				catch(e)
				{}
				
				outArr.push(data);
				
				if(FGS.giftlistFocus == false)
					newElements++;
				
				try
				{
					var stmt = FGS.database.db.createStatement("INSERT OR IGNORE INTO requests VALUES (:d1,:d2,:e0,:e1,:d3,:d4,:d5,:d6,:d7,:e2,:e3)");
					
					stmt.params.e0 = 0;
					stmt.params.e1 = stmt.params.e2 = stmt.params.e3 = '';	
					
					for(var i in data)
					{
						var j = parseInt(i)+1;
						if(typeof(data[i]) == 'undefined')
							data[i] = '';
						
						stmt.params['d'+j.toString()] = data[i];
					}
					stmtArr.push(stmt);
				}
				catch(a)
				{
				}
			});
			
			FGS.database.db.executeAsync(stmtArr, stmtArr.length, 
			{
				handleCompletion: function(aReason) 
				{
					if(outArr.length > 0)
					{
						FGS.sendView('addNewRequest', '', '', outArr);
						FGS.newElements += newElements;
						FGS.database.updateStats('requests', updStatObj);
					}
					FGS.updateIcon();
				}
			});
		}
	});
}


FGS.database.likeBonus = function(bonusID)
{
	var stmt = FGS.database.db.createStatement("UPDATE bonuses SET like_bonus = 1 where id = :id");
	stmt.params.id = bonusID;
	stmt.executeAsync();
}

FGS.database.commentBonus = function(bonusID)
{
	var stmt = FGS.database.db.createStatement("UPDATE bonuses SET comment_bonus = 1 where id = :id");
	stmt.params.id = bonusID;
	stmt.executeAsync();
}

FGS.database.clearFailedBonuses = function(gameID)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM bonuses where status = 1 and gameID = :gameID and error != '' and error IS NOT NULL");
	stmt.params.gameID = gameID;
	stmt.executeAsync();
}

FGS.database.clearFailedGifts = function(gameID)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM requests where status = 1 and gameID = :gameID and error != '' and error IS NOT NULL");
	stmt.params.gameID = gameID;
	stmt.executeAsync();
}

FGS.database.clearLimitErrors = function(gameID)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM bonuses where status = 1 and gameID = :gameID and error = :error");
	stmt.params.gameID = gameID;
	stmt.params.error = 'limit';
	stmt.executeAsync();
}

FGS.database.clearReceivingErrors = function(gameID)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM bonuses  where status = 1 and gameID = :gameID and error = :error");
	stmt.params.gameID = gameID;
	stmt.params.error = 'receiving';
	stmt.executeAsync();	
}

FGS.database.clearRequestReceivingErrors = function(gameID)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM requests where status = 1 and gameID = :gameID and error = :error");
	stmt.params.gameID = gameID;
	stmt.params.error = 'receiving';
	stmt.executeAsync();
}

FGS.database.clearRequestLimitErrors = function(gameID)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM requests where status = 1 and gameID = :gameID and error = :error");
	stmt.params.gameID = gameID;
	stmt.params.error = 'limit';
	stmt.executeAsync();
}

FGS.database.clearByGameID = function(gameID)
{
	var stmt1 = FGS.database.db.createStatement("DELETE FROM bonuses where gameID = :gameID");
	var stmt2 = FGS.database.db.createStatement("DELETE FROM requests 	where gameID = :gameID");
	var stmt3 = FGS.database.db.createStatement("DELETE FROM freegifts 	where gameID = :gameID");
	
	stmt1.params.gameID = stmt2.params.gameID = stmt3.params.gameID = gameID;
	
	stmt1.executeAsync();	stmt2.executeAsync();	stmt3.executeAsync();
}

FGS.database.deleteItem = function(table, gameID, id, status)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM "+table+" where status = :status and gameID = :gameID and id = :id");
	stmt.params.gameID = gameID;
	stmt.params.status = status;
	stmt.params.id = id;
	stmt.executeAsync();
}

FGS.database.deleteItemArray = function(table, gameID, query, status)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM "+table+" "+query+" and status = :status and gameID = :gameID");
	//stmt.params.table = table;
	stmt.params.gameID = gameID;
	stmt.params.status = status;
	stmt.executeAsync();
}

FGS.database.clearTable = function(table, gameID, status)
{
	if(table == 'freegifts')
	{
		var stmt = FGS.database.db.createStatement("DELETE FROM freegifts where gameID = :gameID");
		stmt.params.gameID = gameID;
		stmt.executeAsync();		
	}
	else
	{
		var stmt = FGS.database.db.createStatement("DELETE FROM "+table+" where status = :status and gameID = :gameID");
		//stmt.params.table = table;
		stmt.params.gameID = gameID;
		stmt.params.status = status;
		stmt.executeAsync();
	}
}

FGS.database.deleteItemByTitle = function(table, gameID, title, status)
{
	var stmt = FGS.database.db.createStatement("DELETE FROM "+table+" where status = :status and gameID = :gameID and title = :title");
	//stmt.params.table = table;
	stmt.params.gameID = gameID;
	stmt.params.status = status;
	stmt.params.title = title;
	stmt.executeAsync();
}

FGS.database.deleteOlderThan = function(table, status, time)
{
	if(table == 'freegifts')
	{
		var stmt = FGS.database.db.createStatement("DELETE FROM freegifts where time < :time");
		stmt.params.time = time;
		stmt.executeAsync();
	}
	else
	{
		var stmt = FGS.database.db.createStatement("DELETE FROM "+table+" where status = :status and time < :time");
		//stmt.params.table = table;
		stmt.params.status = status;
		stmt.params.time = time;
		stmt.executeAsync();
	}
}

FGS.database.updateItem = function(table, itemID, info)
{

	var textQry = "status = 1, time = :time";
	var tmpParams = {time: info.time};
	
	if(info.text !== '' && typeof(info.text) !== 'undefined')
	{
		textQry += ", text = :text";
		tmpParams.text = info.text;
	}
	if(info.title !=='' && typeof(info.title) !== 'undefined')
	{
		textQry += ", title = :title";
		tmpParams.title = info.title;
	}
	
	if(info.image !== '' && typeof(info.image) !== 'undefined')
	{
		textQry += ", image = :image";
		tmpParams.image = info.image;
	}
	
	if(info.thanks !=='' && typeof(info.thanks) !== 'undefined')
	{
		textQry += ", resend_gift = :resend_gift";
		tmpParams.resend_gift = JSON.stringify(info.thanks);
	}
	
	if(info.error_text !=='' && typeof(info.error_text) !== 'undefined')
	{
		textQry += ", error_text = :error_text";
		tmpParams.error_text  = info.error_text;
	}
	
	var stmt = FGS.database.db.createStatement("UPDATE "+table+" SET "+textQry+" where id = '"+itemID+"'");
	
	for(var pID in tmpParams)
	{
		stmt.params[pID] = tmpParams[pID];
	}
	stmt.executeAsync();
}

FGS.database.updateItemGiftBack = function(table, itemID)
{
	var temp = {sent: true};
	var tempStr = JSON.stringify(temp);
	
	var stmt = FGS.database.db.createStatement("UPDATE "+table+" SET resend_gift = :str where id = '"+itemID+"'");
	stmt.params.str = tempStr;
	stmt.executeAsync();
}

FGS.database.updateErrorItem = function(table, itemID, info)
{
	var textQry = "status = 1, error = :error, time = :time, image = :image";
	var tmpParams = 
	{
		error: info.error,
		time: info.time,
		image: info.image	
	};

	if(typeof(info.error_text) !== 'undefined')
	{
		textQry += ", error_text = :error_text";
		tmpParams.error_text  = info.error_text;
	}
	
	var stmt = FGS.database.db.createStatement("UPDATE "+table+" SET "+textQry+" where id = '"+itemID+"'");
	
	for(var pID in tmpParams)
	{
		stmt.params[pID] = tmpParams[pID];
	}
	stmt.executeAsync();
}


FGS.database.updateErrorText = function(table, itemID, text)
{
	var stmt = FGS.database.db.createStatement("UPDATE "+table+" SET error_text = :str where id = '"+itemID+"'");
	stmt.params.str = text;
	stmt.executeAsync();
}

FGS.database.addFreegift = function(gameID, friend, gift, time, thankYou)
{
	var isThankYou = (thankYou == 'undefined' ? 0 : 1);
	
	
	var stmt = FGS.database.db.createStatement("INSERT INTO freegifts (gameID, friend, gift, time, is_thank_you) VALUES(:d1,:d2,:d3,:d4,:d5)");
	stmt.params.d1 = gameID;
	stmt.params.d2 = friend;
	stmt.params.d3 = gift;
	stmt.params.d4 = time;
	stmt.params.d5 = isThankYou;
	
	stmt.executeAsync();
}

